﻿using AutoFlatnessTest.Api;
using Dapper;
using Dapper.Contrib.Extensions;
using MiniExcelLibs;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AutoFlatnessTest.Db
{
    public class DemaRecordDb
    {
        public static bool Add(DemaRecord testRecord)
        {
            using (var connection = DbHelp.GetSQLiteDataCenterConn())
            {
                testRecord.Id = DbHelp.ExplicitKey;
                testRecord.IsSync = 1;
                testRecord.CreatedTime = DateTime.Now;

                connection.Insert<DemaRecord>(testRecord);
            }

            MainWindow.taskTime.AddQueueTask((object obj) =>
            {
                try
                {
                    MainWindow.VModel.SetTbIn();

                    var aaa = (DemaRecord)obj;

                    var err = Cmcc.PlcheckAddAsync(aaa).Result;
                    if (!string.IsNullOrEmpty(err))
                    {
                        MainWindow.VModel.SetTbErr(err);
                        return;
                    }

                    using (var connection = DbHelp.GetSQLiteDataCenterConn())
                    {
                        var sql = @"update DemaRecord SET IsSync=2 WHERE id=@Id";
                        connection.Execute(sql, new { aaa.Id });
                    }
                }
                catch (Exception ex)
                {
                    MainWindow.VModel.SetTbErr(ex.Message);
                }
                finally
                {
                    MainWindow.VModel.SetTbOk();
                }

            }, testRecord);

            return true;
        }

        public static List<GetDemaPageDto> GetPage(TestRecordPageDto dto, out int pageCount)
        {
            string where = string.Empty;
            if (dto.TimeGo != null)
                where += " and CreatedTime>@TimeGo";
            if (dto.TimeTo != null)
                where += " and CreatedTime<@TimeTo";

            using (var connection = DbHelp.GetSQLiteDataCenterConn())
            {
                string sql = $@"
select *
from DemaRecord
where 1=1{where}
order by CreatedTime desc
{dto.GetPageSql()}
";

                string sql2 = $@"
select count(1)
from DemaRecord
where 1=1{where}";

                pageCount = connection.QueryFirstOrDefault<int>(sql2, dto);
                return connection.Query<GetDemaPageDto>(sql, dto).ToList();
            }
        }

        public static bool GetPageExcel(TestRecordPageDto dto, string path)
        {
            string where = string.Empty;
            if (dto.TimeGo != null)
                where += " and CreatedTime>@TimeGo";
            if (dto.TimeTo != null)
                where += " and CreatedTime<@TimeTo";

            using (var connection = DbHelp.GetSQLiteDataCenterConn())
            {
                string sql = $@"
select
TestNum as 编号,
iif(IsOk==1,'合格','不合格') as 通过,
MiSimi as 扫描相似度,
MiMaxVal as 扫描最大值,
MiMinVal as 扫描最小值,
MiVal as 扫描差值,
MiInfo as 扫描原始信息,
strftime('%Y-%m-%d %H:%M:%S',CreatedTime) as 生成时间
from DemaRecord
where 1=1{where}
order by CreatedTime desc";
                connection.Open();
                var reader = connection.ExecuteReader(sql, dto);
                MiniExcel.SaveAs(path, reader, overwriteFile: true);
                return true;
            }
        }

        /// <summary>
        /// 得到所有的可用月份
        /// </summary>
        /// <returns></returns>
        public static List<int> GetYueAll(int nian)
        {
            using (var connection = DbHelp.GetSQLiteDataCenterConn())
            {
                string sql = $@"select distinct strftime('%m',CreatedTime) as day from DemaRecord where CreatedTime>='{nian}-01-01' and CreatedTime<'{nian + 1}-01-01' order by CreatedTime desc";
                return connection.Query<int>(sql).ToList();
            }
        }

        /// <summary>
        /// 得到统计图标数据
        /// </summary>
        /// <param name="nian"></param>
        /// <param name="yue"></param>
        /// <returns></returns>
        public static List<TestRecordDayDattaDto> GetDayData(int nian, int yue)
        {
            DateTime dt1 = new DateTime(nian, yue, 1);
            DateTime dt2 = dt1.AddMonths(1);

            using (var connection = DbHelp.GetSQLiteDataCenterConn())
            {
                string sql = @"
select
strftime('%d',CreatedTime) as day,
count(1) count,
count(IsOk==1 or null) okcount,
count(IsOk!=1 or null) nookcount
from DemaRecord
where CreatedTime>@dt1 and CreatedTime<@dt2
group by day
order by day";
                return connection.Query<TestRecordDayDattaDto>(sql, new { dt1, dt2 }).ToList();
            }
        }
    }
}
